How to Resolve Database Sync Issue

Steps to be followed to resolve Database sync issue between Primary and Secondary databases.

Following changes needs to be performed in the database directory:

Location of the postgres configuration file : /var/lib/pgsql/9.5/data/postgresql.conf

Primary Server:

synchronous_commit = on -------------------------> Needs to be uncommented and ensure that the value is ‘on’

synchronous_standby_names = '<db2_ip>' ---> Needs to be uncommented and add the standby db’s IP if there are multiple standby db’s add ‘*’

Standby Server:

synchronous_commit = on --------------------------> Needs to be uncommented and ensure that the is ‘on’

Below are the steps to be followed:

Stop pgpool service in the slave pgpool-II server

Step 1: Login to standby pgpool

Switch to root user

 # systemctl stop pgpool.service

Stop pgpool service in the master pgpool-II server

Step 2: Login to master pgpool

Switch to root user

 # systemctl stop pgpool.service

Stop postgresql service in the standby db

 # systemctl stop postgresql-9.5

Step 4: Login to primary db server and modify postgres configuration file

vi /var/lib/pgsql/9.5/data/postgresql.conf

synchronous_commit = on

synchronous_standby_names = '<db2_ip>'

And then restart postgresql service using command "systemctl restart postgresql-9.5"

Step 5: Login to standby db server and modify postgres configuration file

vi /var/lib/pgsql/9.5/data/postgresql.conf

synchronous_commit = on

After change done restart the postgresql service using command "systemctl restart postgresql-9.5"

# systemctl restart pgpool.service